import xlrd
from xlutils.copy import copy
from datetime import datetime
class ExcelUtil:
    def __init__(self,file_name):
        self.file_name = file_name
        self.open_excel()
        self.sheet = self.scan_excel()
        self.price_col = 5 #定价所在的列
        self.discount_col = 6  # 折扣所在的列
        self.write_data = copy(self.open_excel())
        self.write_sheet = self.write_data.get_sheet(0)
        self.cost_list = [] #进价数据
        self.sale_list = [] #销售价数据
        self.profit_list = [] #利润

    def open_excel(self):
        try:
            file_handler = xlrd.open_workbook(self.file_name)
            return  file_handler
        except Exception as e:
            print(e)
    #读取表格，返回表格对象
    def scan_excel(self):
        handler = self.open_excel()
        sheet = handler.sheets()[0]
        return sheet
    #获取行数
    def get_lines(self):
        rows = self.sheet.nrows
        if rows >= 1:
            return  rows
        return  None
    #获取列数
    def get_cols(self):
        cols = self.sheet.ncols
        if cols >= 1:
            return  cols
        return None
    #表单中的定价数据
    def get_price(self):
        return self.sheet.col_values(self.price_col,1)
    #表单中折扣数据
    def get_discount(self):
        return self.sheet.col_values(self.discount_col,1)
    #进价计算
    def cost(self):
        price = self.get_price()
        discount = self.get_discount()
        nrows = self.get_lines()
        for index in range(nrows - 1):
            cost = price[index] * discount[index] * 0.01
            self.cost_list.append(round(cost,2))
        return self.cost_list
    #销售价
    def sale(self):
        discount = [0.75,0.8,0.85,0.9,0.95]
        nrows = self.get_lines()
        price_list = self.get_price()
        for row_index in range(nrows - 1):
            cost = self.cost_list[row_index]
            for value in discount:
                sale = price_list[row_index] * value
                profit = sale - cost
                if profit < 6.5:
                    if value == 0.95:
                        self.profit_list.append(profit)
                        self.sale_list.append(sale)
                        break
                    else:
                        continue
                else:
                    self.profit_list.append(profit)
                    self.sale_list.append(sale)
                    break
        return self.sale_list

    #写入表单值
    def write_value(self,row,col,value):
        if row == 0:
            self.write_sheet.write(row, col, value)
        else:
            self.write_sheet.write(row,col,round(value,2))
    #写入操作
    def write(self):
        nrows = self.get_lines()
        ncols = self.get_cols()
        self.cost()
        self.sale()
        self.write_value(0,ncols,'进价')
        self.write_value(0, ncols + 1, '销售价')
        self.write_value(0, ncols + 2, '利润')
        for index in range(nrows - 1):
            self.write_value(index + 1,ncols,self.cost_list[index])
            self.write_value(index + 1,ncols + 1,self.sale_list[index])
            self.write_value(index + 1,ncols + 2,self.profit_list[index])
        now = datetime.now()
        date_str = now.date().strftime('%Y-%m-%d')
        file_name = '淘宝更新' + date_str + '.xls'
        self.write_data.save(file_name)

if __name__ == '__main__':
    excel = ExcelUtil(file_name='青松阁.xls')
    excel.write()

